package com.example.poi.controller.download;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.meyoung.shianyun_jg.entity.Czjl.Czjl;
import com.meyoung.shianyun_jg.entity.enterprise.Enterprise;
import com.meyoung.shianyun_jg.mapper.Czjl.CzjlMapper;
import com.meyoung.shianyun_jg.mapper.department.DepartmentMapper;
import com.meyoung.shianyun_jg.mapper.enterprise.BussinessInfoMapper;
import com.meyoung.shianyun_jg.mapper.enterprise.EnterpriseMapper;
import com.meyoung.shianyun_jg.mapper.type.EnterpriseFoodStatisticsMapper;
import com.meyoung.shianyun_jg.service.GetCstatusService;
import com.meyoung.shianyun_jg.service.type2.SelectTypeServiceNew;
import com.meyoung.shianyun_jg.util.EscapeUnescape;

/** 
* @author 作者 zhujf: 
* @version 创建时间：2017年9月19日 上午9:46:10 
* 
*/
@Controller
@RequestMapping("requestnew")
public class ExportExcelControllerNew extends HttpServlet{

	
	/**
	 * @param 定义版本号
	 */
	private static final long serialVersionUID = 1L;
	@Autowired
	private BussinessInfoMapper bussinessInfoMapper;
	@Autowired
	private SelectTypeServiceNew selectTypeServiceNew;
	@Autowired
	private  EnterpriseFoodStatisticsMapper enterpriseFoodStatisticsMapper;
	//解决静态方法中注入问题
	private static ExportExcelControllerNew excel;
	@Autowired
	private  GetCstatusService cstatusService;
	@Autowired
	private SelectTypeServiceNew typeServiceNew;
	@PostConstruct  
    public void init() {  
		excel = this;  
		excel.bussinessInfoMapper =this.bussinessInfoMapper;
    }  
	/**
	 * @author zhujf
	 * @param request 接收客户端请求
	 * @param response 响应客户端请求
	 * @throws ServletException
	 * @throws IOException
	 */
	@RequestMapping(value = "exportaction")
	@ResponseBody
	public void doGet(HttpServletRequest request, HttpServletResponse response,@RequestParam(value = "type", required = false)String type,@RequestParam(value = "tablename", required = false)String tablename)
	
			throws ServletException, IOException{
				//要 清空response
				response.reset();
				// 设置response的Header
				/*
				response.setContentType("application/octet-stream;charset=utf-8");  */
				/*response.setContentType("application/vnd.ms-excel;charset=iso-8859-1");*/
				response.setContentType("application/msexcel");//设置生成的文件类型 
				
				String name=tablename;
				String userAgent=request.getHeader("User-Agent");
				if (  (userAgent.indexOf("Trident")>0) && (userAgent.toUpperCase().indexOf("MSIE") > 0 || userAgent.indexOf("rv") > 0)){  
					name = URLEncoder.encode(name, "UTF-8");  
				} else {  
					name = new String(name.getBytes("UTF-8"), "ISO8859-1");  
				}
				response.addHeader("Content-Disposition","attachment;filename=" + name + ".xlsx");
		
		if("bbqy".equals(type)){
			
			String[] names = new String[]{"企业名称","营业执照","经营地址","法定代表人","监管单位","企业类型","联系电话","企业状态","预警信息数"};
			//导出文件
			OutputStream outputStream = response.getOutputStream();
			CreateFile(request,names,type,tablename,outputStream,response);
			outputStream.close();
		}else if("spqy".equals(type)){
			String[] names = new String[]{"企业名称","营业执照","经营地址","法定代表人","监管单位","企业类型","联系电话","企业状态","预警信息数"};
			//导出文件
			OutputStream outputStream = response.getOutputStream();
			CreateFile(request,names,type,tablename,outputStream,response);
			outputStream.close();
		}
		
		
	}
	
	
	/**
	 * 
	 * @param request 请求requst
	 * @param names  生成文件的标题头
	 * @param type   是哪个里面的导出
	 * @param tablename 生成文件的名字
	 * @param outputStream 响应客户端的流
	 * @param response 请求响应
	 * @throws IOException
	 */
	@SuppressWarnings("unchecked")
	public  void CreateFile (HttpServletRequest request,String[] names,String type,String tablename,OutputStream outputStream,HttpServletResponse response) throws IOException{
		Object[] obj = creatwork(names);
		
		
		
		
		if("bbqy".equals(type)){
			Map<String,Object> map = (Map<String, Object>) request.getSession().getAttribute("bbornobb");
			//获取结果集
			map.put("page_size", "1000000");
			List<Map<String,Object>> maplist = excel.bussinessInfoMapper.queryEnterpriseInfoList(map);
			// 循环将数据写入Excel
			Map<String,Object> qqmap = new HashMap<String,Object>();
			if(maplist != null || maplist.size()!=0){
				for (int i = 0; i < maplist.size(); i++) {
					obj[2]= ((HSSFSheet)obj[1]).createRow((int) i + 1);  
					qqmap = maplist.get(i);  
					// 创建单元格，设置值       
					((HSSFRow)obj[2]).createCell(0).setCellValue(qqmap.get("ENTERPRISE_NAME") == null?"":qqmap.get("ENTERPRISE_NAME").toString());        
					((HSSFRow)obj[2]).createCell(1).setCellValue(qqmap.get("BUSSINESS_CODE") == null?"":qqmap.get("BUSSINESS_CODE").toString());        
					((HSSFRow)obj[2]).createCell(2).setCellValue(qqmap.get("ADDRESS") == null?"":qqmap.get("ADDRESS").toString());        
					((HSSFRow)obj[2]).createCell(3).setCellValue(qqmap.get("LEGAL") == null?"":qqmap.get("LEGAL").toString());        
					((HSSFRow)obj[2]).createCell(4).setCellValue(qqmap.get("DEPT_NAME")==null?"":qqmap.get("DEPT_NAME").toString());        
					((HSSFRow)obj[2]).createCell(5).setCellValue(cstatusService.getCstatsByString(qqmap.get("ENTERPRISE_TYPE")==null?"": qqmap.get("ENTERPRISE_TYPE").toString()));        
					((HSSFRow)obj[2]).createCell(6).setCellValue(qqmap.get("WARMING_INFO")==null?"": qqmap.get("WARMING_INFO").toString());        
					((HSSFRow)obj[2]).createCell(7).setCellValue(cstatusService.getCstatus(qqmap.get("ENTERPRISE_ID").toString()).get("name").toString());   
					List<String> list = new ArrayList<String>();
					list.add(qqmap.get("ENTERPRISE_ID").toString());
					((HSSFRow)obj[2]).createCell(8).setCellValue(String.valueOf(selectTypeServiceNew.queryWarmingCount(list)));   
					}
			}
		}else if("spqy".equals(type)){
			Map<String,Object> map = (Map<String, Object>) request.getSession().getAttribute("aaornoaa");
			//获取结果集
			List<Enterprise> maplist = enterpriseFoodStatisticsMapper.getEnterpriseList(map);
			// 循环将数据写入Excel
			Enterprise enterprise = new Enterprise();
			if(maplist != null && maplist.size()>0){
				for (int i = 0; i < maplist.size(); i++) {
					obj[2]= ((HSSFSheet)obj[1]).createRow((int) i + 1);  
					enterprise = maplist.get(i);  
					// 创建单元格，设置值        
					((HSSFRow)obj[2]).createCell(0).setCellValue(enterprise.getEnterpriseName() == null?"":enterprise.getEnterpriseName());        
					((HSSFRow)obj[2]).createCell(1).setCellValue(enterprise.getBusinessCode() == null?"":enterprise.getBusinessCode());        
					((HSSFRow)obj[2]).createCell(2).setCellValue(enterprise.getCommAddress() == null?"":enterprise.getCommAddress());        
					((HSSFRow)obj[2]).createCell(3).setCellValue(enterprise.getLegalPerson()== null?"":enterprise.getLegalPerson());        
					((HSSFRow)obj[2]).createCell(4).setCellValue(enterprise.getDeptCode()==null?"":enterprise.getDeptCode());        
					((HSSFRow)obj[2]).createCell(5).setCellValue(cstatusService.getCstatsByString(enterprise.getSubjectTrade()==null?"": enterprise.getSubjectTrade()));        
					((HSSFRow)obj[2]).createCell(6).setCellValue(enterprise.getMobile()==null?"": enterprise.getMobile());        
					((HSSFRow)obj[2]).createCell(7).setCellValue(cstatusService.getCstatus(enterprise.getEnterpriseId()).get("name").toString());   
					List<String> list = new ArrayList<String>();
					list.add(enterprise.getEnterpriseId());
					((HSSFRow)obj[2]).createCell(8).setCellValue(String.valueOf(selectTypeServiceNew.queryWarmingCount(list)));   
					}
			}
		}		
		
		String path =request.getSession().getServletContext().getRealPath("");
		//获取客户端响应流
		
		//将文件写入响应流
		((HSSFWorkbook)obj[0]).write(outputStream); 
		
		
	}
	
	/**
	 * 提取工作表
	 */
		public   Object[] creatwork(String[] names){
			
			// 1.创建一个workbook，对应一个Excel	文件
			HSSFWorkbook wb = new HSSFWorkbook();
					// 2.在workbook中添加一个sheet，对应Excel中的一个sheet
			HSSFSheet sheet = wb.createSheet("b");
					// 3.在sheet中添加表头第0行
					 HSSFRow row = sheet.createRow((int) 0);
					// 生成一个样式，用来设置标题样式
					 HSSFCellStyle style = wb.createCellStyle();
					// 居中格式
					style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
					
					// 设置这些样式
					style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
					style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
					style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
					style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
					style.setBorderRight(HSSFCellStyle.BORDER_THIN);
					style.setBorderTop(HSSFCellStyle.BORDER_THIN);
					style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
					// 生成一个字体
					HSSFFont font = wb.createFont();
					font.setColor(HSSFColor.VIOLET.index);
					font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
					// 把字体应用到当前的样式
					style.setFont(font);
					
					
					// 生成并设置另一个样式,用于设置内容样式
					HSSFCellStyle style2 = wb.createCellStyle();
					style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
					style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
					style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
					style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
					style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
					style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
					style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
					style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
					// 生成另一个字体
					HSSFFont font2 = wb.createFont();
					font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
					// 把字体应用到当前的样式
					style2.setFont(font2);
					
					// 设置表格默认列宽度为15个字符
					sheet.setDefaultColumnWidth(20);
			
					
					
					HSSFCell cell =null;
					   
					
					
					
					
					for(int i = 0; i<names.length;i++){
						cell = row.createCell(i);
						cell.setCellValue(names[i]);
						cell.setCellStyle(style);
					}
					
					Object[] objarr = new Object[]{wb,sheet,row};
					
					
					
			        return objarr;
		}
		
}
